﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Diagnostics;
using System.Windows.Forms;
using org.in2bits.MyXls;
using Ader.TemplateEngine;

namespace Relocation.Com.Tools
{
	/// <summary>
	/// 从DataGridView导出数据到Excel
	/// </summary>
	public class ExcelHelp
	{
		/// <summary>
		/// 执行打印
		/// </summary>
		/// <param name="dataGridView"></param>
		/// <param name="filePath">文件路径</param>
		static public void DataGridToExcel(DataGridView dataGridView)
		{
			DataGridToExcel(dataGridView, null);
		}

		static public void DataGridToExcel(DataGridView dataGridView, VauleFormaterHandler formater)
		{
			String fileFullName = GetFileName();	//获取文件完整路径
			if (String.IsNullOrEmpty(fileFullName))
				return;
			String filePath = Path.GetDirectoryName(fileFullName);	//文件夹
			String fileName = Path.GetFileName(fileFullName);		//文件名
			DataGridViewColumnCollection columns = dataGridView.Columns;
			StringBuilder sb = new StringBuilder();
			List<String> columnNames = new List<string>();

			try
			{
				foreach (DataGridViewColumn column in columns)
				{
					if (!column.Visible)
						continue;
					String headerText = column.HeaderText;
					if (!headerText.StartsWith(","))
						headerText = "&" + headerText;
					sb.Append(headerText);
					columnNames.Add(column.Name);
				}
				if (sb.Length > 0)
				{
					sb.Remove(0, 1);
				}
			} catch (Exception)
			{

			}

			String names = sb.ToString();
			String[] columnTexts = names.Split('&');
			//是否又多行表头
			bool isMulitLine = names.Contains(',');
			int rowIndex = 1;
			XlsDocument xls = new XlsDocument();
			XF xfTitle = xls.NewXF();
			xfTitle.HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
			xfTitle.VerticalAlignment = VerticalAlignments.Centered;
			xfTitle.Font.Bold = true;



			Worksheet sheet = xls.Workbook.Worksheets.Add("sheet");
			Cells cells = sheet.Cells;

			int leave = 0;
			TreeView treeView = GetTreeView(dataGridView, ref leave);
			//绘制表头
			makeHeads(treeView, cells, xfTitle, leave);
			rowIndex += leave - 1;

			//数据            
			foreach (DataGridViewRow row in dataGridView.Rows)
			{
				rowIndex++;
				int cellIndex = 1;  //起点为1
				foreach (String colName in columnNames)
				{
					if (!dataGridView.Columns[colName].Visible)
						continue;
					XF xfValue = xls.NewXF();
					DataGridViewCell g_cell = row.Cells[colName];
					if (g_cell == null)
						continue;

					Object value = g_cell.FormattedValue;
					Type type = g_cell.FormattedValueType;

					String valueString = "";

					
					if (type.Equals(typeof(decimal)) || type.Equals(typeof(System.Nullable<decimal>)) || g_cell.Style.Format.ToLower().Equals("n2"))
					{
						if (value != null)
						{
							valueString = ((decimal)value).ToString("G0");
						} else
						{
							valueString = "0";
						}
						xfValue.UseNumber = true;
						xfValue.HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Right;
					} else if (type.Equals(typeof(DateTime)) || type.Equals(typeof(System.Nullable<DateTime>)))
					{
						if (value != null)
						{
							valueString = ((DateTime)value).ToString("yyyy年MM月dd日");
						}
					} else
					{
						if (value != null)
						{
							valueString = value.ToString();
						}
					}
					
					cells.Add(rowIndex, cellIndex++, valueString, xfValue);
				}

			}
			xls.FileName = fileName;
			xls.Save(filePath, true);

			if (System.IO.File.Exists(fileFullName))
			{
				Process.Start(fileFullName);
			}
		}



		/// <summary>
		/// 绘制表头
		/// </summary>
		/// <param name="treeView">多行表头对象</param>
		/// <param name="cells">sheet.Cells</param>
		/// <param name="xf">表头的单元格样式</param>
		/// <param name="leave">表头最大深度（多行表头）</param>
		static private void makeHeads(TreeView treeView, Cells cells, XF xf, int leave)
		{
			int colIndex = 1;
			foreach (TreeNode node in treeView.Nodes)
			{
				makeHeads(node, cells, ref colIndex, leave, xf);
			}
		}

		static private void makeHeads(TreeNode treeNode, Cells cells, ref int colIndex, int leave, XF xf)
		{
			int colCount = getNodeColCount(treeNode);   //此节点占用的列数
			int rowCount = getChildsRowCount(treeNode, leave);  //此节点占用的行数
			int rowIndex = treeNode.Level + 1;
			cells.Add(rowIndex, colIndex, treeNode.Text, xf);

			cells.Merge(rowIndex, rowIndex + rowCount - 1, colIndex, colIndex + colCount - 1);
			if (treeNode.Nodes.Count > 0)
			{
				foreach (TreeNode node in treeNode.Nodes)
				{
					makeHeads(node, cells, ref colIndex, leave, xf);
				}
			} else
			{
				colIndex += colCount;

			}

		}

		static private TreeView GetTreeView(DataGridView dataGridView, ref int maxLevel)
		{
			TreeView tnc = new TreeView();
			foreach (DataGridViewColumn column in dataGridView.Columns)
			{
				if (!column.Visible)
					continue;
				string[] headTexts = column.HeaderText.Split(',');
				if (maxLevel < headTexts.Length)
					maxLevel = headTexts.Length;
				TreeNode pnode = null;
				for (int i = 0; i < headTexts.Length; i++)
				{
					if (headTexts[i] == "")
					{
						if (i == 0)
							pnode = (tnc.Nodes.Count > 0) ? tnc.Nodes[tnc.Nodes.Count - 1] : null;
						else
							pnode = pnode.Nodes[pnode.Nodes.Count - 1];
					} else
					{
						TreeNode node = new TreeNode(headTexts[i]);
						if (i == 0)
							tnc.Nodes.Add(node);
						else
							pnode.Nodes.Add(node);
						pnode = node;
					}
				}

			}

			return tnc;
		}


		/// <summary>
		/// 根据DataGridView生成多行表头的TreeVIew
		/// </summary>
		/// <param name="dataGridView">dataGridView</param>
		/// <returns>TreeView</returns>
		static private TreeView GetTreeView(DataGridView dataGridView)
		{
			TreeView tnc = new TreeView();
			foreach (DataGridViewColumn column in dataGridView.Columns)
			{
				string[] headTexts = column.HeaderText.Split(',');

				TreeNode pnode = null;
				for (int i = 0; i < headTexts.Length; i++)
				{

					if (headTexts[i] == "")
					{
						if (i == 0)
							pnode = (tnc.Nodes.Count > 0) ? tnc.Nodes[tnc.Nodes.Count - 1] : null;
						else
							pnode = pnode.Nodes[pnode.Nodes.Count - 1];
					} else
					{
						TreeNode node = new TreeNode(headTexts[i]);
						if (i == 0)
							tnc.Nodes.Add(node);
						else
							pnode.Nodes.Add(node);
						pnode = node;
					}
				}
			}

			return tnc;
		}

		static private int getMaxLevel(TreeView treeView)
		{
			TreeNode treeNode = null;
			foreach (TreeNode node in treeView.Nodes)
			{
				TreeNode _node = getMaxLevelNode(node);
				if (treeNode == null)
				{
					treeNode = _node;
				} else
				{
					if (treeNode.Level < _node.Level)
						treeNode = _node;
				}
			}
			return treeNode == null ? 0 : treeNode.Level;
		}

		/// <summary>
		/// 返回最深的节点（如果有多个，返回第一个）
		/// </summary>
		/// <param name="node"></param>
		/// <returns></returns>
		static private TreeNode getMaxLevelNode(TreeNode node)
		{
			if (node == null)
				return null;
			if (node.Nodes.Count > 0)
			{
				bool hasChild = false;
				foreach (TreeNode _node in node.Nodes)
				{
					if (_node.Nodes.Count > 0)
					{
						hasChild = true;
						break;
					}

				}
				if (hasChild)
				{
					TreeNode treeNode = null;
					foreach (TreeNode _node in node.Nodes)
					{
						TreeNode tempNode = getMaxLevelNode(_node);
						if (treeNode == null)
						{
							treeNode = tempNode;
						} else
						{
							if (treeNode.Level < tempNode.Level)
								treeNode = tempNode;
						}
					}
					return treeNode;
				} else
				{
					return node.Nodes[0];
				}
			} else
			{
				return node;
			}
		}

		/// <summary>
		/// 返回当前节点占用多少列
		/// </summary>
		/// <param name="node"></param>
		/// <returns>（因为包含本身，所以至少占用一列）</returns>
		static private int getNodeColCount(TreeNode node)
		{
			int colCount = getChildsCount(node, 0);
			return colCount == 0 ? 1 : colCount;
		}

		static private int getChildsCount(TreeNode node, int count)
		{
			if (node == null)
				return count;
			if (node.Nodes.Count < 1)
				return count;
			foreach (TreeNode _node in node.Nodes)
			{
				if (_node.Nodes.Count > 0)
				{
					count = getChildsCount(_node, count);
				} else
				{
					count++;
				}
			}
			return count;
		}

		static private int getChildsRowCount(TreeNode node, int maxLevel)
		{
			if (node == null || maxLevel < 1)
				return 1;
			return node.Nodes.Count > 0 ? 1 : maxLevel - node.Level;
		}

		/// <summary>
		/// 弹出对话框，选择文件保存路径
		/// </summary>
		/// <returns>返回目标文件的路径，如果用户取消选择则返回null</returns>	
		static private String GetFileName()
		{

			return GetFileName(null);
		}

		/// <summary>
		/// 弹出保存文件对话框
		/// </summary>
		/// <param name="fileName">默认文件名</param>
		/// <returns>文件路径</returns>
		static private String GetFileName(String fileName)
		{
			SaveFileDialog dialog = new SaveFileDialog();
			dialog.DefaultExt = ".xls";
			dialog.AddExtension = true;
			dialog.CheckPathExists = true;
			dialog.Filter = "Excel电子表格 (*.xls)|*.xls";
			if (!string.IsNullOrEmpty(fileName))
				dialog.FileName = fileName;
			DialogResult dialogResult = dialog.ShowDialog();
			if (!dialogResult.Equals(DialogResult.OK))
				return null;
			return dialog.FileName;
		}	

	

		

		static int GetRandomSeed()
		{
			byte[] bytes = new byte[4];
			System.Security.Cryptography.RNGCryptoServiceProvider rng = new System.Security.Cryptography.RNGCryptoServiceProvider();
			rng.GetBytes(bytes);
			return BitConverter.ToInt32(bytes, 0);
		}
		private static int GetRandom(int max, int min)
		{
			Random random = new Random(GetRandomSeed());
			return random.Next(min, max);
		}

	}

	public delegate string VauleFormaterHandler(Object value,string fieldName);

}
